package com.zzy.jkb.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;

/**
 * @author zzy
 * @Date 2022/8/31 13:50
 * @description:
 */
public class PoiExcel {

    public static void excelport(HttpServletResponse response) throws Exception {

        //数据来源 通过参数传入
        String[] masterHead = {"学号","姓名","专业"};
        String[] childHead = {"课程名称","上课地点","任课教师","上课时间"};
        List<String[]> childList = new ArrayList<>();
        childList.add(new String[]{"Java程序设计","1号楼302","雷老师","2022/8/30 15:53:49"});
        childList.add(new String[]{"数据结构","1号楼305","雷老师","2022/8/30 9:18:28"});
        List<String[]> childList1 = new ArrayList<>();
        childList1.add(new String[]{"计算机网络","2号楼301","方老师","2022/8/30 15:53:49"});
        List<Map<String,List<String[]>>> masterList = new ArrayList<>();
        Map<String,List<String[]>> map = new HashMap();
        map.put("20210211-张晓-计算机与科学",childList);
        map.put("20210212-于丽-电子信息工程",childList1);
        masterList.add(map);
        //创建Excel工作薄对象
        HSSFWorkbook workbook=new HSSFWorkbook();
        //创建Excel工作表对象
        HSSFSheet sheet = workbook.createSheet("wj");
        //设置单元格居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        //创建行的单元格，从0开始
        HSSFRow row = sheet.createRow(0);

        //创建统计单元格
        HSSFCell masterCell=row.createCell(0);
        //赋值
        masterCell.setCellValue("主表");
        masterCell.setCellStyle(cellStyle);
        //合并列
        CellRangeAddress region=new CellRangeAddress(0, 0, 0, masterHead.length-1);
        sheet.addMergedRegion(region);

        //创建详情单元格  从统计单元格的后一格开始创建
        HSSFCell childCell = row.createCell(masterHead.length);
        //赋值
        childCell.setCellValue("子表");
        childCell.setCellStyle(cellStyle);
        //合并列
        region=new CellRangeAddress(0, 0, masterHead.length, masterHead.length+childHead.length-1);
        sheet.addMergedRegion(region);

        //表头 从1开始
        HSSFRow titleRow = sheet.createRow(1);
        //主表头
        for (int i = 0; i < masterHead.length ; i++) {
            HSSFCell msCell = titleRow.createCell(i);
            msCell.setCellStyle(cellStyle);
            msCell.setCellValue(masterHead[i]);
        }
        //子表头
        for (int i = 0; i < childHead.length; i++) {
            HSSFCell chcell = titleRow.createCell(masterHead.length+i);
            chcell.setCellStyle(cellStyle);
            chcell.setCellValue(childHead[i]);
        }

        //填充数据
        int lastRowIndex = 2; //记录最后行位置
        for (Map<String,List<String[]>> m : masterList){
            for (String key : m.keySet()){
                String[] ms = key.split("-");
                List<String[]> chlist = m.get(key);
                HSSFRow valueRow = sheet.createRow(lastRowIndex);
                for (int i = 0; i < ms.length ; i++) {
                    HSSFCell mscell = valueRow.createCell(i);
                    mscell.setCellStyle(cellStyle);
                    mscell.setCellValue(ms[i]);
                    if (chlist.size()>1){ //子表数量大于1才进行 行合并
                        region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, i, i);
                        sheet.addMergedRegion(region);
                    }
                }
                for (int i = 0; i < chlist.size(); i++) {
                    String[] chstrs = chlist.get(i);
                    HSSFRow chRow;
                    if (i == 0){ //避免重复创建 覆盖主表数据
                        chRow = valueRow;
                    }else {
                        chRow  = sheet.createRow(lastRowIndex);
                    }
                    lastRowIndex++;
                    for (int j = 0; j < chstrs.length; j++) {
                        HSSFCell chcell = chRow.createCell(ms.length+j);
                        chcell.setCellStyle(cellStyle);
                        chcell.setCellValue(chstrs[j]);
                    }
                }
            }
        }

        String fileName = URLEncoder.encode("POIExcel下载测试","UTF-8");
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.setHeader("Content-Disposition","attachment;filename="+fileName+".xls");
        OutputStream os = response.getOutputStream();
        workbook.write(os);
        os.flush();
        os.close();
        workbook.close();
    }
}

